package dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import jdbc.jdbc;
import pojo.ClassTable;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ClasstableDaoImp implements ClasstableDao {
    /**
     * 新增班级表数据
     * @param classtable
     * @return
     * @throws SQLException
     */
    @Override
    public int insert(ClassTable classtable) throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql增加语句
        String sql="insert into schooltreasure.classtable (classId,className)values('"+classtable.getClassId()+"','"+classtable.getClassName()+"');";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        int res=preparedStatement.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,preparedStatement,connection);
        return 0;
    }

    /**
     * 删除班级表数据
     * @param classtable
     * @return
     * @throws SQLException
     */
    @Override
    public int del_Update(ClassTable classtable) throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql删除语句
        String sql="update schooltreasure.classtable set invalid='"+classtable.getInvalid()+"' where id='"+classtable.getId()+"'";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        int res=preparedStatement.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,preparedStatement,connection);
        return 0;
    }


    /**
     * 修改班级表数据
     * @param classtable
     * @return
     */
    @Override
    public int update(ClassTable classtable) throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql删除语句
        String sql="update schooltreasure.classtable set classId='"+classtable.getClassId()+"',className='"+classtable.getClassName()+"' where id='"+classtable.getId()+"'";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        int res=preparedStatement.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,preparedStatement,connection);
        return 0;
    }

    /**
     * 显示班级表数据
     * @return
     */
    @Override
    public JSONArray ShowClass() throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql语句
        String dou="or";
        String sql="SELECT * FROM schooltreasure.classtable";

        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        ResultSet res=preparedStatement.executeQuery();//执行sql语句，无论什么语句都能执行

        JSONArray jsonArray=new JSONArray();
        while (res.next()){
            JSONObject jsonObject=new JSONObject();
            if(res.getInt("invalid")!=1){
                jsonObject.put("id",res.getInt("id"));
                jsonObject.put("classId",res.getInt("classId"));
                jsonObject.put("className",res.getString("className"));
                jsonObject.put("invalid",res.getInt("invalid"));
                jsonArray.add(jsonObject);
            }

        }
        jdbc.guanbi(res,preparedStatement,connection);
        return jsonArray;
    }
}
